package beans; 

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import domainObjects.Dossier;
import util.ConnectionDataSource;

public class DossierBean 
{ 
 public ArrayList<Dossier> getDossiers() throws SQLException { 
	 ConnectionDataSource cds = new ConnectionDataSource();
	 Connection conn = cds.getSimpleConnection();
	 
	 java.sql.Statement stmt = null;
	 ResultSet results = null;
	 ArrayList<Dossier> dossiers = new ArrayList<Dossier>();
		try {
			stmt = conn.createStatement();
			String query = "SELECT \"ID\", \"Nom\", \"Prenom\", \"NumeroPlaque\", \"NumeroPermis\" FROM \"Dossier\"";
			results = stmt.executeQuery(query);
			while (results.next()) {
				dossiers.add(new Dossier(results.getLong(1), results.getString(2), results.getString(3), results.getString(5), results.getString(4)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
 			if (stmt != null&& !stmt.isClosed()) { stmt.close();}
 			if (conn != null && !conn.isClosed()) { conn.close();}
 			if (results != null && !results.isClosed()) { results.close();}
 		}		
	 return dossiers;
 } 
 public void addDossier(String nom,String prenom,String noPermis,String noPlaque) throws SQLException
 {
		if (nom.trim() == "") {
			throw new IllegalArgumentException("nom");
		}
		if (prenom.trim() == "") {
			throw new IllegalArgumentException("prenom");
		}
		if (noPermis.trim() == "") {
			throw new IllegalArgumentException("permis");
		}
		if (noPlaque.trim() == "") {
			throw new IllegalArgumentException("plaque");
		}
	 ConnectionDataSource cds = new ConnectionDataSource();
	 Connection conn = cds.getSimpleConnection();
	 
	 PreparedStatement pstmt = null;
	    try { 
	   	  String queryPermis = "SELECT \"ID\", \"Nom\", \"Prenom\", \"NumeroPlaque\", \"NumeroPermis\" FROM \"Dossier\" Where \"NumeroPermis\" = ?";
	   	  PreparedStatement pstmtPermis = conn.prepareStatement(queryPermis);
	   	  pstmtPermis.setString(1, noPermis);
	      ResultSet dossiers = pstmtPermis.executeQuery();
	      if(dossiers.next()){
	    	  throw new IllegalArgumentException("permis");
	      }
	      String query ="INSERT INTO \"Dossier\"(\"Nom\", \"Prenom\", \"NumeroPlaque\", \"NumeroPermis\") VALUES (?, ?, ?, ?);";
	      pstmt = conn.prepareStatement(query); // create a statement
	      pstmt.setString(1, nom); // set input parameter 1
	      pstmt.setString(2, prenom); // set input parameter 2
	      pstmt.setString(3, noPlaque); // set input parameter 3
	      pstmt.setString(4, noPermis); // set input parameter 4
	      pstmt.executeUpdate(); // execute insert statement
	    } catch (SQLException e) {
	      e.printStackTrace();
	    } finally {
	    	if (pstmt != null&& !pstmt.isClosed()) { pstmt.close();}
 			if (conn != null && !conn.isClosed()) { conn.close();}
	    } 	 
 }
 public ArrayList<Dossier> findDossiers(String nom, String prenom) throws SQLException
 {
	 ArrayList<Dossier> dossiers = new ArrayList<Dossier>();
	 if((prenom == null || prenom.trim() == "") && (nom == null || nom.trim() == "")){
		 return dossiers;
	 }
	 ConnectionDataSource cds = new ConnectionDataSource();
	 Connection conn = cds.getSimpleConnection();
	 PreparedStatement pstmt = null;
		 try { 
		 if(prenom == null || prenom.trim() == "")
		 {
	   	  String query = "SELECT \"ID\", \"Nom\", \"Prenom\", \"NumeroPlaque\", \"NumeroPermis\" FROM \"Dossier\" Where \"Nom\" = ?";
	   	  pstmt = conn.prepareStatement(query);
	   	  pstmt.setString(1, nom);	      
		 }
		 else if (nom == null || nom.trim() == "")
		 {
			  String query = "SELECT \"ID\", \"Nom\", \"Prenom\", \"NumeroPlaque\", \"NumeroPermis\" FROM \"Dossier\" Where \"Prenom\" = ?";
		   	  pstmt = conn.prepareStatement(query);
		   	  pstmt.setString(1, prenom);	      
		 }
		 else
		 {
			 String query = "SELECT \"ID\", \"Nom\", \"Prenom\", \"NumeroPlaque\", \"NumeroPermis\" FROM \"Dossier\" Where \"Prenom\" = ? and \"Nom\" = ?";
		   	  pstmt = conn.prepareStatement(query);
		   	  pstmt.setString(1, prenom);
		   	  pstmt.setString(2, nom);	
		 }
		 ResultSet results = pstmt.executeQuery();
			while (results.next()) {
				dossiers.add(new Dossier(results.getLong(1), results.getString(2), results.getString(3), results.getString(5), results.getString(4)));
			}	 
	 }catch (SQLException e) {
     e.printStackTrace();
   } finally {
   	if (pstmt != null&& !pstmt.isClosed()) { pstmt.close();}
		if (conn != null && !conn.isClosed()) { conn.close();}
   } 
   return dossiers;
 }
 public ArrayList<Dossier> findDossiers(String noPlaque) throws SQLException
 {
	 ConnectionDataSource cds = new ConnectionDataSource();
	 Connection conn = cds.getSimpleConnection();
	 PreparedStatement pstmt = null;
	 ArrayList<Dossier> dossiers = new ArrayList<Dossier>();
	 if(noPlaque == null)
	 {
		 return dossiers;
	 }
	 try { 
		
	   	  String query = "SELECT \"ID\", \"Nom\", \"Prenom\", \"NumeroPlaque\", \"NumeroPermis\" FROM \"Dossier\" Where \"NumeroPlaque\" = ?";
	   	  pstmt = conn.prepareStatement(query);
	   	  pstmt.setString(1, noPlaque);	      
		 ResultSet results = pstmt.executeQuery();
			while (results.next()) {
				dossiers.add(new Dossier(results.getLong(1), results.getString(2), results.getString(3), results.getString(5), results.getString(4)));
			}	 
	 }catch (SQLException e) {
		 return new ArrayList<Dossier>();
   } finally {
   	if (pstmt != null&& !pstmt.isClosed()) { pstmt.close();}
		if (conn != null && !conn.isClosed()) { conn.close();}
   } 
   return dossiers;
 }
 public ArrayList<Dossier> findDossier(String noPermis) throws SQLException
 {
	 ConnectionDataSource cds = new ConnectionDataSource();
	 Connection conn = cds.getSimpleConnection();
	 PreparedStatement pstmt = null;
	 ArrayList<Dossier> dossiers = new ArrayList<Dossier>();
	 if(noPermis == null)
	 {
		 return dossiers;
	 }
	 try { 
		
	   	  String query = "SELECT \"ID\", \"Nom\", \"Prenom\", \"NumeroPlaque\", \"NumeroPermis\" FROM \"Dossier\" Where \"NumeroPermis\" = ?";
	   	  pstmt = conn.prepareStatement(query);
	   	  pstmt.setString(1, noPermis);	      
		 ResultSet results = pstmt.executeQuery();
			while (results.next()) {
				dossiers.add(new Dossier(results.getLong(1), results.getString(2), results.getString(3), results.getString(5), results.getString(4)));
			}	 
	 }catch (SQLException e) {
		 return dossiers;
   } finally {
   	if (pstmt != null&& !pstmt.isClosed()) { pstmt.close();}
		if (conn != null && !conn.isClosed()) { conn.close();}
   } 
   return dossiers;
 }
 public Dossier findDossier(long id) throws SQLException
 {
	 ConnectionDataSource cds = new ConnectionDataSource();
	 Connection conn = cds.getSimpleConnection();
	 PreparedStatement pstmt = null;
	 ArrayList<Dossier> dossiers = new ArrayList<Dossier>();
	 try { 
		
	   	  String query = "SELECT \"ID\", \"Nom\", \"Prenom\", \"NumeroPlaque\", \"NumeroPermis\" FROM \"Dossier\" Where \"ID\" = ?";
	   	  pstmt = conn.prepareStatement(query);
	   	  pstmt.setLong(1, id);	      
		  ResultSet results = pstmt.executeQuery();
			while (results.next()) {
				return new Dossier(results.getLong(1), results.getString(2), results.getString(3), results.getString(5), results.getString(4));
			}	 
	 }catch (SQLException e) {
     e.printStackTrace();
   } finally {
   	if (pstmt != null&& !pstmt.isClosed()) { pstmt.close();}
		if (conn != null && !conn.isClosed()) { conn.close();}
   } 
   return null;
 }
}